<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use think\Loader;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
//系统导出
class Xitongexcel extends Common
{
	//导出excel
	 public function excelmo($data,$title){
   

        Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);

       for($i=0;$i<count($data["content"]);$i++){
             $objPHPExcel->getActiveSheet()->setCellValue(chr($i+97).'1',$data["title"][$i]);
          
             //加粗
             $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getFont()->setBold(true);
             //水平剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //填充背景色
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getFill()->getStartColor()->setARGB('00ff3322');
           
           $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'1')->getAlignment()->setWrapText(true);


             $objPHPExcel->getActiveSheet()->setCellValue(chr($i+97).'2',$data["content"][$i]);
               //水平剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

             //填充背景色
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'2')->getFill()->getStartColor()->setARGB('00FFFF00');
            
            $objPHPExcel->getActiveSheet()->getStyle(chr($i+97).'2')->getAlignment()->setWrapText(true);
             //设置宽度
             $objPHPExcel->getActiveSheet()->getColumnDimension(chr($i+97))->setWidth(30);
            
       }
            
            //设置高度
             $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
       for($i=0;$i<=20;$i++){
        $h='A'.($i+3);
         $objPHPExcel->getActiveSheet()->setCellValue($h," ");
       }
     //***********************画出单元格边框*****************************  
    $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
    $objPHPExcel->getActiveSheet()->getStyle('A1:'.chr(96+count($data["content"])).'2')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  
//***********************画出单元格边框结束*****************************  
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        //var_dump($objWriter);exit;
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename='.$title.'模版.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');

    }
	public function jituan(){

		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"集团目标格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        //echo $ishas;exit;
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a"
        	];
        }
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('jituan');
	}
	public function jituanbc(){
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];

       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

        if($data["channel"])
        	$typedata["channel_id"]=$data["channel_id"];
        else{
        	$typedata["channel_id"]=null;
        }
        if($data["client"])
        	$typedata["client_id"]=$data["client_id"];
        else{
        	$typedata["client_id"]=null;
        }
        if($data["product"])
        	$typedata["product_id"]=$data["product_id"];
        else{
        	$typedata["product_id"]=null;
        }
        if($data["feiyong"])
        	$typedata["feiyong_id"]=$data["feiyong_id"];
        else{
        	$typedata["feiyong_id"]=null;
        }
        $typedata["people_id"]=$data["people_id"];
        $typedata["sale_id"]=$data["sale_id"];
        $typedata["sale_real_id"]=$data["sale_real_id"];
        $typedata["profit_id"]=$data["profit_id"];
        $typedata["kaoheqi"]=$data["kaoheqi"];

      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"集团目标格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");

        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
        //return json_encode($data);
	}
	public function jituan_daochu(){
		$title="集团目标格式";
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		->join("field fc","tf.channel_id=fc.id","LEFT")
		->join("field fcl","tf.client_id=fcl.id","LEFT")
		->join("field fp","tf.product_id=fp.id","LEFT")
		->join("field ff","tf.feiyong_id=ff.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("fc.field_name as channel,fcl.field_name as client,fp.field_name as product,ff.field_name as feiyong,tf.people_id,tf.sale_id,tf.sale_real_id,tf.profit_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		if($tmp["channel"]){
			$biaoti[]=$tmp["channel"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["channel"]}类型";
		}
		if($tmp["client"]){
			$biaoti[]=$tmp["client"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["client"]}";
		}
		if($tmp["product"]){
			$biaoti[]="产品".$tmp["product"];
			$cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
		}
		if($tmp["feiyong"]){
			$biaoti[]=$tmp["feiyong"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["feiyong"]}";
		}
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
		if($tmp["people_id"]){
			$biaoti[]="人员纬度";
			$cont[]="必填项。";
		}
		if($tmp["sale_id"]){
			$biaoti[]="毛销售金额(元)";
			$cont[]="必填项。";
		}
		if($tmp["sale_real_id"]){
			$biaoti[]="净销售金额(元)";
			$cont[]="必填项。";
		}
		if($tmp["profit_id"]){
			$biaoti[]="利润目标金额(元)";
			$cont[]="必填项。";
		}

		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
	/////////TI费用预算
	public function tiyuasuan(){

		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"TI费用预算格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a"
        	];
        }
        //echo $ishas;exit;
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('tiyuasuan');
	}

	public function tiyuansuanbc(){
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];
//echo $type_name;exit;
       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

        if($data["channel"])
        	$typedata["channel_id"]=$data["channel_id"];
        else{
        	$typedata["channel_id"]=null;
        }
        if($data["area"]){
            $tmp=Db::table("field")->alias("s")->join("field f","s.pid=f.id")->where(["s.id"=>$data["area_id"]])->field("f.field_name as fname")->find();
            if($tmp["fname"]=="区域"){
        	   $typedata["area_id"]=$data["area_id"];$typedata["client_id"]=null;
            }
            else{
                $typedata["client_id"]=$data["area_id"];$typedata["area_id"]=null;
            }
            
        }
        else{
        	$typedata["area_id"]=null;
            $typedata["client_id"]=null;
        }
        if($data["product"])
        	$typedata["product_id"]=$data["product_id"];
        else{
        	$typedata["product_id"]=null;
        }
        if($data["feiyong"])
        	$typedata["feiyong_id"]=$data["feiyong_id"];
        else{
        	$typedata["feiyong_id"]=null;
        }
        $typedata["people_id"]=$data["people_id"];
       
        $typedata["kaoheqi"]=$data["kaoheqi"];

      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"TI费用预算格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");
        	//print_r($typedata);exit;
        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
        //return json_encode($data);
	}
	public function tiyuasuan_daochu(){
		$title="TI费用预算格式";
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		->join("field fc","tf.channel_id=fc.id","LEFT")
		->join("field fa","tf.area_id=fa.id","LEFT")
		->join("field fp","tf.product_id=fp.id","LEFT")
		->join("field ff","tf.feiyong_id=ff.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
        ->join("field fcl","tf.client_id=fcl.id","LEFT")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("fc.field_name as channel,fcl.field_name as client,fa.field_name as area,fp.field_name as product,ff.field_name as feiyong,tf.people_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		if($tmp["channel"]){
			$biaoti[]=$tmp["channel"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["channel"]}类型";
		}
		 if($tmp["client"]){
            $biaoti[]=$tmp["client"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["client"]}";
        }
		if($tmp["product"]){
			$biaoti[]="产品".$tmp["product"];
			$cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
		}
        if($tmp["area"]){
            $biaoti[]=$tmp["area"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["area"]}";
        }
		if($tmp["feiyong"]){
			$biaoti[]=$tmp["feiyong"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["feiyong"]}";
		}
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
		if($tmp["people_id"]){
			$biaoti[]="人员纬度";
			$cont[]="必填项。";
		}
		$biaoti[]="预算金额(元)";
        $cont[]="必填项";
		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
	/////////AP费用预算
	public function apyuasuan(){

		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"AP费用预算格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a"
        	];
        }
        //echo $ishas;exit;
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('apyuasuan');
	}
	public function apyuansuanbc(){
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];
//echo $type_name;exit;
       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

        if($data["channel"])
        	$typedata["channel_id"]=$data["channel_id"];
        else{
        	$typedata["channel_id"]=null;
        }


         if($data["area"]){
            $tmp=Db::table("field")->alias("s")->join("field f","s.pid=f.id")->where(["s.id"=>$data["area_id"]])->field("f.field_name as fname")->find();
            if($tmp["fname"]=="区域"){
               $typedata["area_id"]=$data["area_id"];$typedata["client_id"]=null;
            }
            else{
                $typedata["client_id"]=$data["area_id"];$typedata["area_id"]=null;
            }
            
        }
        else{
            $typedata["area_id"]=null;
            $typedata["client_id"]=null;
        }
        if($data["product"])
        	$typedata["product_id"]=$data["product_id"];
        else{
        	$typedata["product_id"]=null;
        }
        if($data["feiyong"])
        	$typedata["feiyong_id"]=$data["feiyong_id"];
        else{
        	$typedata["feiyong_id"]=null;
        }
        $typedata["people_id"]=$data["people_id"];
       
        $typedata["kaoheqi"]=$data["kaoheqi"];

      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=> $type_name,"tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");
        	//print_r($typedata);exit;
        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
        //return json_encode($data);
	}
	public function apyuasuan_daochu(){
		$title="AP费用预算格式";
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		->join("field fc","tf.channel_id=fc.id","LEFT")
		->join("field fcl","tf.client_id=fcl.id","LEFT")
		->join("field fp","tf.product_id=fp.id","LEFT")
		->join("field ff","tf.feiyong_id=ff.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
        ->join("field fa","tf.area_id=fa.id","LEFT")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("fc.field_name as channel,fa.field_name as area,fcl.field_name as client,fp.field_name as product,ff.field_name as feiyong,tf.people_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		if($tmp["channel"]){
			$biaoti[]=$tmp["channel"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["channel"]}类型";
		}
		if($tmp["client"]){
            $biaoti[]=$tmp["client"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["client"]}";
        }
		if($tmp["product"]){
			$biaoti[]="产品".$tmp["product"];
			$cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
		}
         if($tmp["area"]){
            $biaoti[]=$tmp["area"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["area"]}";
        }
		if($tmp["feiyong"]){
			$biaoti[]=$tmp["feiyong"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["feiyong"]}";
		}
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
		if($tmp["people_id"]){
			$biaoti[]="人员纬度";
			$cont[]="必填项。";
		}
		$biaoti[]="预算金额(元)";
        $cont[]="必填项";
		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
	//////日常费用格式
	///
	///
	///
	public function rcyuasuan(){

		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"日常费用预算格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        //echo $ishas;exit;
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a","depart_id"=>""
        	];
        }
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('rcyuasuan');
	}
	public function rcyuansuanbc(){
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];
//echo $type_name;exit;
       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

     // echo $typedata["type_id"];exit;
        if($data["feiyong"])
        	$typedata["feiyong_id"]=$data["feiyong_id"];
        else{
        	$typedata["feiyong_id"]=null;
        }
        $typedata["depart_id"]=$data["depart_id"];
       
        $typedata["kaoheqi"]=$data["kaoheqi"];

      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=> $type_name,"tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");
        	//print_r($typedata);exit;
        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
        //return json_encode($data);
	}
	public function rcyuasuan_daochu(){
		$title="日常费用预算格式";
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		
		->join("field ff","tf.feiyong_id=ff.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("ff.field_name as feiyong,tf.depart_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		
		if($tmp["feiyong"]){
			$biaoti[]=$tmp["feiyong"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["feiyong"]}";
		}
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
		if($tmp["depart_id"]){
			$biaoti[]="部门纬度";
			$cont[]="必填项。必须是系统中存在的部门";
		}
		$biaoti[]="预算金额(元)";
        $cont[]="必填项";
		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
	////经销商销售目标格式
	public function jingxiaoshang(){
		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>"经销商销售目标格式","tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        //echo $ishas;exit;
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a","depart_id"=>""
        	];
        }
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('jingxiaoshang');
	}
	public function jingxiaoshangbc(){
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];
//echo $type_name;exit;
       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

     // echo $typedata["type_id"];exit;
        $far=Db::table("field")->where(["field_name"=>"经销商"])->find();
        $typedata["client_id"]=$far["id"];
        if($data["product"])
        	$typedata["product_id"]=$data["product_id"];
        else{
        	$typedata["product_id"]=null;
        }
        
       
        $typedata["kaoheqi"]=$data["kaoheqi"];
        
      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=> $type_name,"tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");
        	//print_r($typedata);exit;
        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
	}
	public function jingxiaoshang_daochu(){
		$title="经销商销售目标格式";
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		->join("field fp","tf.product_id=fp.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("fp.field_name as product,tf.depart_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		$biaoti[]="经销商";
        $cont[]="必填项。必须是系统中已存在的经销商";
		if($tmp["product"]){
			$biaoti[]="产品".$tmp["product"];
			$cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
		}
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
        
		$biaoti[]="销售目标(元)";
        $cont[]="必填";
		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
	/////个人销售目标
	public function geren($tid=1){
		switch ($tid) {
			case 2:
				$tname="个人回款目标格式";
				break;
			case 3:
				$tname="个人零售目标格式";
				break;
			default:
				$tid=1;
				$tname="个人发运目标格式";
				break;
		}

		$list=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>$tname,"tf.is_actived"=>1,"tf.is_deleted"=>0])->select();
        //print_r($target);
        $ishas=count($target);
        if(!$ishas){
        	$target[0]=["channel_id"=>"a","client_id"=>"a","product_id"=>"a","area_id"=>"a","feiyong_id"=>"a",
        		"people_id"=>1,"sale_id"=>1,"sale_real_id"=>1,"profit_id"=>1,"kaoheqi"=>"a"
        	];
        }
        //echo $ishas;exit;
        $this->assign("tid",$tid);
        $this->assign("tname",$tname);
        $this->assign("target",$target);
        $this->assign("ishas",$ishas);
		$this->assign("list",$list);
		return $this->fetch('geren');
	}
	public function gerenbc(){

	
		 $request=Request::instance();
        $data=$request->post();

          $type_name=$data["type_name"];
//echo $type_name;exit;
       //获取type_id
      	$thistype =Db::table("target_format_type")->where(["format_type"=>$type_name])->find();
      	$typedata["type_id"]=$thistype["id"];

        if($data["channel"])
        	$typedata["channel_id"]=$data["channel_id"];
        else{
        	$typedata["channel_id"]=null;
        }
        
         if($data["area"]){
            $tmp=Db::table("field")->alias("s")->join("field f","s.pid=f.id")->where(["s.id"=>$data["area_id"]])->field("f.field_name as fname")->find();
            if($tmp["fname"]=="区域"){
               $typedata["area_id"]=$data["area_id"];$typedata["client_id"]=null;
            }
            else{
                $typedata["client_id"]=$data["area_id"];$typedata["area_id"]=null;
            }
            
        }
        else{
            $typedata["area_id"]=null;
            $typedata["client_id"]=null;
        }
        if($data["product"])
        	$typedata["product_id"]=$data["product_id"];
        else{
        	$typedata["product_id"]=null;
        }
       
       
        $typedata["kaoheqi"]=$data["kaoheqi"];
        $typedata["people_id"]=1;
      
      	//echo $thistype["id"];exit;
      	$tmp=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=> $type_name,"tf.is_actived"=>1,"tf.is_deleted"=>0])->field("tf.id")->find();
        if($tmp){
        	//修改
        	$typedata["id"]=$tmp["id"];
        	$typedata["modifiled_on"]=date("Y-m-d H:i:s");
        	//print_r($typedata);exit;
        	Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 4;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 4;//失败
            }
        }else{
        	//储存
        	$typedata["created_on"]=date("Y-m-d H:i:s");
//print_r($typedata);exit;

        	Db::startTrans(); //开启事物
        	try{

        		$res1=Db::table("target_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return 41;
            }
            if($res1){
                Db::commit(); //事物提交
                return 1;//成功
            }else{
                Db::rollBack();
                return 42;//失败
            }
        }
        //return json_encode($data);
	}
	public function geren_daochu($tid=1){
		switch ($tid) {
			case 2:
				$title="个人回款目标格式";
				break;
			case 3:
				$title="个人零售目标格式";
				break;
			default:
				$tid=1;
				$title="个人发运目标格式";
				break;
		}

		
		$tmp=Db::table("target_format")->alias("tf")
		->join("target_format_type tft","tf.type_id=tft.id")
		->join("field fc","tf.channel_id=fc.id","LEFT")
		->join("field fa","tf.area_id=fa.id","LEFT")
		->join("field fp","tf.product_id=fp.id","LEFT")
		->join("field ff","tf.feiyong_id=ff.id","LEFT")
		->join("field fk","tf.kaoheqi=fk.id")
        ->join("field fcl","tf.client_id=fcl.id","LEFT")
		->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])
		->field("fc.field_name as channel,fcl.field_name as client,fa.field_name as area,fp.field_name as product,ff.field_name as feiyong,tf.people_id,fk.field_name as kaoheqi")
		->find();
		//print_r($tmp);
		$biaoti=[];
		$cont=[];
		if($tmp["channel"]){
			$biaoti[]=$tmp["channel"];
			$cont[]="必填项。必须是系统中已存在的{$tmp["channel"]}类型";
		}
		if($tmp["client"]){
            $biaoti[]=$tmp["client"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["client"]}";
        }
		if($tmp["product"]){
			$biaoti[]="产品".$tmp["product"];
			$cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
		}
		if($tmp["area"]){
            $biaoti[]=$tmp["area"];
            $cont[]="必填项。必须是系统中已存在的{$tmp["area"]}";
        }
		$biaoti[]="财年";
		$cont[]="必填项。必须是系统中已存在的财年";
		$biaoti[]=$tmp["kaoheqi"];
		$cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于财年";
        $biaoti[]="销售人员姓名";
        $cont[]="必填项。必须是系统中已存在的员工";
		$biaoti[]="目标金额（元）";
        $cont[]="必填项";
		
		$data["title"]=$biaoti;
		$data["content"]=$cont;
		$this->excelmo($data,$title);
	}
    //网点下载
    public function wangdian($fid){

        $tmp=Db::table("target_net_format")->alias("tf")
        ->join("field fp","tf.product_id=fp.id","LEFT")
        ->join("field fk","tf.kaoheqi=fk.id")
        ->join("cg_cainian c","tf.cainian=c.id")
        ->where(["tf.id"=>$fid,"tf.is_actived"=>1,"tf.is_deleted"=>0])
        ->field("fp.field_name as product,fk.field_name as kaoheqi,c.title as cainian")
        ->find();
        $biaoti=[];
        $cont=[];
       
        $biaoti[]="网点";
        $cont[]="必填项。必须是系统中已存在的网点";
        if($tmp["product"]){
            $biaoti[]="产品".$tmp["product"];
            $cont[]="必填项。必须是系统中已存在的产品{$tmp["product"]}";
        }
     
      
        $biaoti[]=$tmp["kaoheqi"];
        $cont[]="必填项。必须是系统中已存在的{$tmp["kaoheqi"]}，且该{$tmp["kaoheqi"]}属于{$tmp["cainian"]}财年";
        $biaoti[]="目标金额（元）";
        $cont[]="必填项";
        
        $data["title"]=$biaoti;
        $data["content"]=$cont;
        $title="{$tmp["cainian"]}财年门店销售目标格式";
        $this->excelmo($data,$title);
    }

}